{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>目录<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#组合where字句-and-or\" data-toc-modified-id=\"组合where字句-and-or-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>组合where字句 and or</a></span><ul class=\"toc-item\"><li><span><a href=\"#求值优先级\" data-toc-modified-id=\"求值优先级-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>求值优先级</a></span></li></ul></li><li><span><a href=\"#组合where字句-in\" data-toc-modified-id=\"组合where字句-in-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>组合where字句 in</a></span></li><li><span><a href=\"#组合where字句-not\" data-toc-modified-id=\"组合where字句-not-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>组合where字句 not</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pandas as pd\n",
    "conn = mysql.connector.connect(host='127.0.0.1',\n",
    "                      user='iroan',\n",
    "                      password='iroanMYS47',\n",
    "                      database = 'practice_sql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>vend_id</th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy, complete with bean bag worm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy, eggs are not included</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy, comes with bean bag carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR01</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR02</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR03</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "      <td>18 inch Raggedy Ann doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>King doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch king doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>Queen doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch queen doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id vend_id            prod_name  prod_price  \\\n",
       "0  BNBG01   DLL01    Fish bean bag toy        3.49   \n",
       "1  BNBG02   DLL01    Bird bean bag toy        3.49   \n",
       "2  BNBG03   DLL01  Rabbit bean bag toy        3.49   \n",
       "3    BR01   BRS01    8 inch teddy bear        5.99   \n",
       "4    BR02   BRS01   12 inch teddy bear        8.99   \n",
       "5    BR03   BRS01   18 inch teddy bear       11.99   \n",
       "6  RGAN01   DLL01          Raggedy Ann        4.99   \n",
       "7   RYL01   FNG01            King doll        9.49   \n",
       "8   RYL02   FNG01           Queen doll        9.49   \n",
       "\n",
       "                                           prod_desc  \n",
       "0  Fish bean bag toy, complete with bean bag worm...  \n",
       "1           Bird bean bag toy, eggs are not included  \n",
       "2   Rabbit bean bag toy, comes with bean bag carrots  \n",
       "3       8 inch teddy bear, comes with cap and jacket  \n",
       "4      12 inch teddy bear, comes with cap and jacket  \n",
       "5      18 inch teddy bear, comes with cap and jacket  \n",
       "6                           18 inch Raggedy Ann doll  \n",
       "7    12 inch king doll with royal garments and crown  \n",
       "8   12 inch queen doll with royal garments and crown  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select *from Products;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 组合where字句 and or"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id  prod_price            prod_name\n",
       "0  BNBG01        3.49    Fish bean bag toy\n",
       "1  BNBG02        3.49    Bird bean bag toy\n",
       "2  BNBG03        3.49  Rabbit bean bag toy"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_id,prod_price,prod_name from Products where vend_id = 'DLL01' and prod_price <=4;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name  prod_price\n",
       "0    Fish bean bag toy        3.49\n",
       "1    Bird bean bag toy        3.49\n",
       "2  Rabbit bean bag toy        3.49\n",
       "3    8 inch teddy bear        5.99\n",
       "4   12 inch teddy bear        8.99\n",
       "5   18 inch teddy bear       11.99\n",
       "6          Raggedy Ann        4.99"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_name,prod_price from Products where vend_id = 'DLL01' or vend_id = 'BRS01';'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 求值优先级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name  prod_price\n",
       "0    Fish bean bag toy        3.49\n",
       "1    Bird bean bag toy        3.49\n",
       "2  Rabbit bean bag toy        3.49\n",
       "3   18 inch teddy bear       11.99\n",
       "4          Raggedy Ann        4.99"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_name,prod_price from Products where vend_id = 'DLL01' or vend_id = 'BRS01' and prod_price >=10;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            prod_name  prod_price\n",
       "0  18 inch teddy bear       11.99"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_name,prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >=10;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 组合where字句 in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "      <td>5.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             prod_name  prod_price\n",
       "0   12 inch teddy bear        8.99\n",
       "1   18 inch teddy bear       11.99\n",
       "2    8 inch teddy bear        5.99\n",
       "3    Bird bean bag toy        3.49\n",
       "4    Fish bean bag toy        3.49\n",
       "5  Rabbit bean bag toy        3.49\n",
       "6          Raggedy Ann        4.99"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_name,prod_price from Products where vend_id in('DLL01','BRS01') order by prod_name;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 组合where字句 not"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8 inch teddy bear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>King doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Queen doll</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            prod_name\n",
       "0  12 inch teddy bear\n",
       "1  18 inch teddy bear\n",
       "2   8 inch teddy bear\n",
       "3           King doll\n",
       "4          Queen doll"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = '''select prod_name from Products where not vend_id = 'DLL01' order by prod_name;'''\n",
    "pd.read_sql_query(statement,conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "163px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "目录",
   "title_sidebar": "大纲",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
